﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace NMigrate.Versioning
{
	public class SqlDatabaseVersionHandler : VersionHandler
	{
		private readonly String _ConnectionString;
		public String ConnectionString
		{
			get
			{
				return _ConnectionString;
			}
		}
		public SqlDatabaseVersionHandler(string connectionString)
		{
			_ConnectionString = connectionString;
		}
		#region VersionHandler Members

		public int GetVersion()
		{
			if(!DatabaseExists())
				return 0;
			EnsureTableExists();
			SqlConnection connection = new SqlConnection(ConnectionString);
			connection.Open();
			using(connection)
			{
				var command = connection.CreateCommand();
				command.CommandText = "Select Value From VersioningTable where Parameter='Version'";
				var result = command.ExecuteScalar();
				if(result == null)
				{
					command.CommandText = "Insert Into [VersioningTable] VALUES ('Version','0')";
					command.ExecuteNonQuery();
					return 0;
				}
				return int.Parse(result.ToString());
			}
		}

		private void EnsureTableExists()
		{
			using(var conn = new SqlConnection(ConnectionString))
			{
				conn.Open();
				var command = conn.CreateCommand();
				try
				{
					command.CommandText = "Select * From VersioningTable";
					command.ExecuteNonQuery();
				}
				catch(SqlException ex)
				{
					if(ex.Number == 208)
					{
						command.CommandText = "CREATE TABLE [VersioningTable]([Parameter] [nvarchar](max) NOT NULL,[Value] [nvarchar](max) NOT NULL); Insert Into [VersioningTable] VALUES ('Version','0')";
						command.ExecuteNonQuery();
						return;
					}
					throw;
				}
			}
		}

		private bool DatabaseExists()
		{
			SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
			var database = builder.InitialCatalog;
			builder.InitialCatalog = "master";
			SqlConnection connection = new SqlConnection(builder.ToString());
			connection.Open();
			var command = connection.CreateCommand();
			command.CommandText = "USE [" + database + "]";
			try
			{
				command.ExecuteNonQuery();
			}
			catch(SqlException ex)
			{
				if(ex.Number == 911)
				{
					return false;
				}
				throw;
			}
			return true;
		}

		public void SetVersion(int version)
		{
			EnsureTableExists();
			SqlConnection connection = new SqlConnection(ConnectionString);
			connection.Open();
			using(connection)
			{
				var command = connection.CreateCommand();
				command.CommandText = "UPDATE VersioningTable SET Value=@version where Parameter='Version'";
				command.Parameters.Add(new SqlParameter("@version", version));
				command.ExecuteNonQuery();
			}
		}

		#endregion
	}
}
